package com.chlp.core.framework.utils;

import com.google.common.base.CaseFormat;

import javax.persistence.Column;
import javax.persistence.Id;
import javax.persistence.Table;
import java.lang.reflect.Field;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Collections;
import java.util.List;

/**
 * @author 陈礼鹏
 * @date 2020/1/16 17:23
 */
public class MulTableQuery {

    private static final String LEFT_JOIN = "LEFT JOIN";

    public static Assist getAssist(Object entity) {
        Class<?> oneClass = entity.getClass();
        // SQL帮助类
        Assist assist = new Assist();
        // 1.处理 查询的列
        MulTableQuery.queryColumns(assist, oneClass);
        // 2.处理 查询那些表
        MulTableQuery.queryTableOneTable(assist, oneClass);
        // 3.处理 查询条件
        MulTableQuery.queryCondition(assist, entity, oneClass);
        return assist;
    }

    /**
     * 处理需要查询的列（字段），处理结果有两种情况
     * <p>
     * 1： select A.a_a AS aA , A.b_b AS bB, A.c_c AS cC 【其中A是实体，a_a,b_b,c_c是实体A中的属性】
     * 2： select A.a_a AS A_aA , A.b_b AS A_bB, A.c_c AS cA_C 【其中A是实体，a_a,b_b,c_c是实体A中的属性】
     *
     * @param tables           对象数组，该数组里面所有的对象都需要查询
     * @param asAddTablePrefix 查询的列是否加前缀：true：添加 即select a_b AS ab_ab；false：不添加 即 select a_b as ab
     */
    public static void queryColumns(Assist assist, boolean asAddTablePrefix, List<Class<?>> tables) {
        handleColumns(assist, asAddTablePrefix, tables);
    }

    /**
     * 处理需要查询的列（字段），处理结果有两种情况
     * <p>
     * 1： select A.a_a AS aA , A.b_b AS bB, A.c_c AS cC 【其中A是实体，a_a,b_b,c_c是实体A中的属性】
     * 2： select A.a_a AS A_aA , A.b_b AS A_bB, A.c_c AS cA_C 【其中A是实体，a_a,b_b,c_c是实体A中的属性】
     *
     * @param tables           对象数组，该数组里面所有的对象都需要查询
     * @param asAddTablePrefix 查询的列是否加前缀：true：添加 即select a_b AS ab_ab；false：不添加 即 select a_b as ab
     */
    public static void queryColumns(Assist assist, boolean asAddTablePrefix, Class<?>... tables) {
        handleColumns(assist, asAddTablePrefix, Arrays.asList(tables));
    }

    /**
     * 处理需要查询的列（字段），处理结果如下
     * <p>
     * select A.a_a AS aA , A.b_b AS bB, A.c_c AS cC 【其中A是实体，a_a,b_b,c_c是实体A中的属性】
     *
     * @param tables 对象数组，该数组里面所有的对象都需要查询
     */
    public static void queryColumns(Assist assist, Class<?>... tables) {
        handleColumns(assist, false, Arrays.asList(tables));
    }

    /**
     * 1： select A.a_a AS aA , A.b_b AS bB, A.c_c AS cC 【其中A是实体，a_a,b_b,c_c是实体A中的属性】
     * 2： select A.a_a AS A_aA , A.b_b AS A_bB, A.c_c AS cA_C 【其中A是实体，a_a,b_b,c_c是实体A中的属性】
     *
     * @param asAddTablePrefix 查询的列是否加前缀：true
     */
    private static void handleColumns(Assist assist, boolean asAddTablePrefix, List<Class<?>> tables) {
        String column = assist.getResultColumn();
        StringBuilder resultColumn = new StringBuilder();
        List<String> tablesEntity = new ArrayList<>();
        for (Class<?> object : tables) {
            Field[] fields = object.getDeclaredFields();
            // 实体名首字母小写
            String classSimpleName = toLowerCaseFirstOne(object.getSimpleName());
            if (tablesEntity.contains(classSimpleName)) {
                classSimpleName = classSimpleName + "1";
            } else {
                tablesEntity.add(classSimpleName);
            }
            for (Field field : fields) {
                if (field.toString().contains("static") || field.toString().contains("final")) {
                    continue;
                }

                String fieldName = field.getName();
                resultColumn.append(classSimpleName);
                resultColumn.append(".");

                Column annotation = field.getAnnotation(Column.class);
                if (annotation != null && !"".equals(annotation.name())) {
                    resultColumn.append(annotation.name());
                } else {
                    resultColumn.append(getTableName(object));
                }

                resultColumn.append(" AS ");
                if (asAddTablePrefix) {
                    resultColumn.append(classSimpleName).append("_").append(fieldName);
                } else {
                    resultColumn.append(fieldName);
                }

                resultColumn.append(",");
            }
        }

        if (column != null) {
            assist.setResultColumn(column + "," + resultColumn.substring(0, resultColumn.length() - 1));
        } else {
            assist.setResultColumn(resultColumn.substring(0, resultColumn.length() - 1));
        }
    }

    /**
     * 处理需要查询的列（字段）
     * select xx (用户传什么就查询什么)
     *
     * @param columns 字符串数组，所有的都需要查询
     */
    public static void queryColumns(Assist assist, List<String> columns) {
        String column = assist.getResultColumn();
        StringBuilder resultColumn = new StringBuilder();
        if (column != null) {
            resultColumn.append(",");
            columns.forEach(v -> resultColumn.append(v).append(","));
            String columnAdd = resultColumn.substring(0, resultColumn.length() - 1);
            assist.setResultColumn(column + columnAdd);
        } else {
            columns.forEach(v -> resultColumn.append(v).append(","));
            String columnAdd = resultColumn.substring(0, resultColumn.length() - 1);
            assist.setResultColumn(columnAdd);
        }
    }

    /**
     * 处理需要查询的列（字段）
     * select xx (用户传什么就查询什么)
     *
     * @param columns 字符串数组，所有的都需要查询
     */
    public static void queryColumns(Assist assist, String... columns) {
        String column = assist.getResultColumn();
        StringBuilder resultColumn = new StringBuilder();
        if (column != null) {
            resultColumn.append(",");
            for (String col : columns) {
                resultColumn.append(col).append(",");
            }
            String columnAdd = resultColumn.substring(0, resultColumn.length() - 1);
            assist.setResultColumn(column + columnAdd);
        } else {
            for (String col : columns) {
                resultColumn.append(col).append(",");
            }
            String columnAdd = resultColumn.substring(0, resultColumn.length() - 1);
            assist.setResultColumn(columnAdd);
        }
    }

    /**
     * 处理需要查询的列（字段）
     * select x_x AS xX (会自动添加AS)
     *
     * @param columns 字符串数组，所有的都需要查询
     */
    public static void queryColumnsAddAs(Assist assist, String... columns) {
        String column = assist.getResultColumn();
        StringBuilder resultColumn = new StringBuilder();
        if (column != null) {
            resultColumn.append(",");
            for (String col : columns) {
                resultColumn.append(col).append(" AS ").append(upperCamel(col)).append(",");
            }
            String columnAdd = resultColumn.substring(0, resultColumn.length() - 1);
            assist.setResultColumn(column + columnAdd);
        } else {
            for (String col : columns) {
                resultColumn.append(col).append(" AS ").append(upperCamel(col)).append(",");
            }
            String columnAdd = resultColumn.substring(0, resultColumn.length() - 1);
            assist.setResultColumn(columnAdd);
        }
    }

    /**
     * 处理需要查询的列（字段）
     * select table.x_x AS xX (会自动添加AS)
     *
     * @param columns 字符串数组，所有的都需要查询
     */
    public static void queryColumnsAddPre(Assist assist, String table, String... columns) {
        String column = assist.getResultColumn();
        StringBuilder resultColumn = new StringBuilder();
        if (column != null) {
            resultColumn.append(",");
            for (String col : columns) {
                resultColumn.append(table).append(".").append(col).append(" AS ").append(upperCamel(col)).append(",");
            }
            String columnAdd = resultColumn.substring(0, resultColumn.length() - 1);
            assist.setResultColumn(column + columnAdd);
        } else {
            for (String col : columns) {
                resultColumn.append(table).append(".").append(col).append(" AS ").append(upperCamel(col)).append(",");
            }
            String columnAdd = resultColumn.substring(0, resultColumn.length() - 1);
            assist.setResultColumn(columnAdd);
        }
    }

    /**
     * 单表查询 from a_b表 AS aB;
     */
    public static void queryTableOneTable(Assist assist, Class<?> leftTable, boolean... ifAddAs) {
        if (ifAddAs.length > 0) {
            assist.setTables(getTableName(leftTable));
        } else {
            assist.setTables(getTableName(leftTable) + " AS " + MulTableQuery.toLowerCaseFirstOne(leftTable.getSimpleName()));
        }
    }

    /**
     * 单表查询
     */
    public static void queryTableOneTable(Assist assist, String table) {
        assist.setTables(table);
    }

    /**
     * 默认用左表的主键字段，去关联右表中的包含左表主键的字段
     * <p>
     * 左表和右表必须有相同的字段，才能正常的查询。默认左连接
     *
     * @param leftTableId 默认用左边的主键关联右表
     */
    public static void queryTableLeftJoin(Assist assist, boolean leftTableId, Class<?> leftTable, Class<?> rightTable) {
        String join = null;
        Field[] fields;
        if (leftTableId) {
            fields = leftTable.getDeclaredFields();
        } else {
            fields = rightTable.getDeclaredFields();
        }
        for (Field field : fields) {
            Id id = field.getAnnotation(Id.class);
            if (id != null) {
                Column idField = field.getAnnotation(Column.class);
                join = idField.name();
                break;
            }
        }
        handleJoinTable(leftTable, LEFT_JOIN, rightTable, join, join, assist);
    }

    /**
     * 默认用右边的主键字段，去关联左表的中的包含右表主键的字段
     * <p>
     * 左表和右表必须有相同的字段，才能正常的查询
     *
     * @param join 左边和右边关联 依赖业务传参
     */
    public static void queryTableLeftJoinSameOn(Assist assist, Class<?> leftTable, Class<?> rightTable, String join) {
        handleJoinTable(leftTable, LEFT_JOIN, rightTable, join, join, assist);
    }

    /**
     * 默认用右边的主键字段，去关联左表的中的包含右表主键的字段
     * <p>
     * 左表和右表必须有相同的字段，才能正常的查询
     */
    public static void queryTableRightJoinSameOn(Assist assist, Class<?> leftTable, Class<?> rightTable, String join) {
        String rightJoin = "RIGHT JOIN";
        handleJoinTable(leftTable, rightJoin, rightTable, join, join, assist);
    }

    /**
     * 默认用右边的主键字段，去关联左表的中的包含右表主键的字段
     * <p>
     * 左表和右表必须有相同的字段，才能正常的查询
     */
    public static void queryTableInnerJoinSameOn(Assist assist, Class<?> leftTable, Class<?> rightTable, String join) {
        String innerJoin = "INNER JOIN";
        handleJoinTable(leftTable, innerJoin, rightTable, join, join, assist);
    }

    /**
     * 处理 查询那些表
     * <p>
     * 左表 AS 左表别名 LEFT 右表 AS 右表别名 ON  左表.左连接字段=右表.右连接字段
     *
     * @param leftTable  左表
     * @param type       连接的类型：LEFT JOIN / RIGHT JOIN / INNER JOIN
     * @param rightTable 右表
     * @param leftJoin   左连接字段
     * @param rightJoin  右连接字段
     * @param assist     SQL
     */
    public static void handleJoinTable(Class<?> leftTable, String type, Class<?> rightTable, String leftJoin, String rightJoin,
                                       Assist assist) {
        String tables = assist.getTables();
        StringBuilder table = new StringBuilder();
        // 左表名
        String leftDbTable = getTableName(leftTable);
        // 左实体名 实体首字母转小写
        String leftTableName = toLowerCaseFirstOne(leftTable.getSimpleName());
        // 右表名
        String rightDbTable = getTableName(rightTable);
        // 右实体名 实体首字母转小写
        String rightTableName = toLowerCaseFirstOne(rightTable.getSimpleName());

        // A left
        if (tables == null) {
            table.append(leftDbTable).append(" AS ").append(leftTableName).append("  ").append(type).append("  ");
        } else {
            table.append("  ").append(type).append("  ");
        }
        // B   查询的表是否添加AS，即是否取别名  from xx表 AS xx
        table.append(rightDbTable).append(" AS ").append(rightTableName);
        // .. ON ..
        table.append(" ON ");
        // A.左连接字段
        table.append(leftTableName).append(".").append(leftJoin);
        // =
        table.append("=");
        // B.右连接字段
        table.append(rightTableName).append(".").append(rightJoin);

        if (tables == null) {
            assist.setTables(table.toString());
        } else {
            assist.setTables(assist.getTables() + table.toString());
        }
    }

    /**
     * where a=1 and b =2
     * <p>
     * 拼接SQL的查询条件，即where后面的SQL
     *
     * @param object 该对象一般是前端传过来的，页面的查询条件的属性
     * @param tables 前端传的对象的属性，在那些实体类中有相同的属性
     * @param assist 组合SQL
     */
    public static void queryCondition(Assist assist, Object object, List<Class<?>> tables) {
        handleCondition(assist, object, tables);
    }

    /**
     * where a=1 and b =2
     * <p>
     * 拼接SQL的查询条件，即where后面的SQL
     *
     * @param object 该对象一般是前端传过来的，页面的查询条件的属性
     * @param tables 前端传的对象的属性，在那些实体类中有相同的属性
     * @param assist 组合SQL
     */
    public static void queryCondition(Assist assist, Object object, Class<?>... tables) {
        List<Class<?>> table = new ArrayList<>(Arrays.asList(tables));
        handleCondition(assist, object, table);
    }

    public static void queryConditionOneTable(Assist assist, Object object, Class<?> tables) {
        List<Class<?>> table = new ArrayList<>(Collections.singletonList(tables));
        handleCondition(assist, object, table, false);
    }

    private static void handleCondition(Assist assist, Object object, List<Class<?>> tables, boolean... ifAddAs) {
        if (object == null) {
            return;
        }
        // 获取对象的所有的属性集合
        Field[] fields = getAllFields(object);
        for (Field field : fields) {
            //获取属性名称
            String fieldName;
            //获取属性值
            Object fieldValue = null;
            //获取属性类型
            Class<?> type = field.getType();
            //获取属性类型：int string ...
            String typeName = type.getName();
            // 打开私有访问
            field.setAccessible(true);
            try {
                fieldValue = field.get(object);
            } catch (IllegalAccessException e) {
                e.printStackTrace();
            }
            // 根据传过来的对象数组，找到该属性名称应该是属于那个对象的。并且修改属性名称使之可以作为列查询. ==》 实体.属性下划线 查询条件
            fieldName = getFieldName(tables, field, ifAddAs);
            if (fieldName == null || fieldValue == null || "".equals(fieldValue)) {
                continue;
            }
            if ("int".equals(typeName) || "double".equals(typeName) || "float".equals(typeName) || "java.lang.Integer".equals(typeName)) {
                assist.andEq(fieldName, fieldValue);
            }
            if ("java.lang.String".equals(typeName)) {
                String value = (String) fieldValue;
                if (value.contains("~")) {
                    String[] split = value.split("~");
                    if (split.length == 0) {
                        continue;
                    }
                    String first = split[0];
                    if (first != null && !"".equals(first)) {
                        assist.andGte("DATE_FORMAT(" + fieldName + ",'%Y-%m-%d')", first);
                    }
                    if (split.length == 2) {
                        String second = split[1];
                        if (second != null && !"".equals(second)) {
                            assist.andLte("DATE_FORMAT(" + fieldName + ",'%Y-%m-%d')", second);
                        }
                    }
                } else if (value.contains(";")) {
                    Object[] split = value.split(";");
                    assist.andIn(fieldName, split);
                } else if (value.contains("%")) {
                    assist.andLike(fieldName, value);
                } else {
                    assist.andEq(fieldName, value);
                }
            }
        }
    }

    /**
     * 这个方法通过while循环及getSuperClass()方法获取当前类的父类再进行getDeclaredFields()即可,
     * 注意这个方法返回的是一个Field数组,然后如果我们想要把这些属性拼接到一起,
     * 当然是使用链表更方便一些,利用Arrays.asList()方法将数组转化为链表,
     * 注意:这个方法返回的是一个List<>也就是抽象列表,所以要将其再用ArrayList初始化一次得到的列表才可变,否则得到的是一个不可变的列表
     */
    private static Field[] getAllFields(Object object) {
        Class<?> clazz = object.getClass();
        List<Field> fieldList = new ArrayList<>();
        while (clazz != null) {
            fieldList.addAll(new ArrayList<>(Arrays.asList(clazz.getDeclaredFields())));
            clazz = clazz.getSuperclass();
        }
        Field[] fields = new Field[fieldList.size()];
        fieldList.toArray(fields);
        return fields;
    }

    /**
     * 处理SQL中的where查询条件，判断属性在那个实体类中存在
     *
     * @param tables 实体
     * @param field  属性，sql字段
     * @return 表对应的字段
     */
    private static String getFieldName(List<Class<?>> tables, Field field, boolean... ifAddAs) {
        String fieldName = field.getName();
        Column annotation = field.getAnnotation(Column.class);
        // 判断tables里面的属性是否包含fieldName，如果包含则继续进行添加查询条件，否则继续
        boolean conclude = false;
        for (Class<?> table : tables) {
            try {
                table.getDeclaredField(fieldName);
                // 如果改属性有Column注解，那么优先取注解，否则取属性转下划线的值
                if (annotation != null && !"".equals(annotation.name())) {
                    fieldName = annotation.name();
                } else {
                    // 将驼峰转成下划线
                    fieldName = getTableName(table);
                }
                if (ifAddAs.length <= 0) {
                    // 如果需要添加前缀，则把实例首字符小写作为前缀
                    fieldName = MulTableQuery.toLowerCaseFirstOne(table.getSimpleName()) + "." + fieldName;
                }

                conclude = true;
                break;
            } catch (NoSuchFieldException ignored) {
            }
        }
        if (conclude) {
            return fieldName;
        } else {
            return null;
        }
    }


    /**
     * 首字母转小写
     */
    private static String toLowerCaseFirstOne(String s) {
        if (Character.isLowerCase(s.charAt(0))) {
            return s;
        } else {
            return Character.toLowerCase(s.charAt(0)) + s.substring(1);
        }
    }

    /**
     * 首字母转小写
     * 如果实体中有start和length，则会执行分页查询，否则执行非分页查询
     * 如果存在length但是业务传的值是：0，则代表不需要分页
     */
    public static Integer[] handlePageParameter(Object pageBean) {
        int page = 1;
        Integer pageSize = 10;
        Class<?> pageBeanClass = pageBean.getClass();
        try {
            Field start = pageBeanClass.getDeclaredField("start");
            // 打开私有访问
            start.setAccessible(true);
            Integer startValue = (Integer) start.get(pageBean);
            Field length = pageBeanClass.getDeclaredField("length");
            // 打开私有访问
            length.setAccessible(true);
            Integer lengthValue = (Integer) length.get(pageBean);
            if (startValue != null) {
                pageSize = lengthValue;
                if (!startValue.equals(0)) {
                    page = startValue / pageSize + 1;
                }
            }
        } catch (Exception e) {
            return new Integer[]{};
        }
        return new Integer[]{page, pageSize};
    }


    /**
     * 分页处理
     * 1.如果length和start值都是：0，则代表不需要分页
     * 2.如果start和length有一个值不是：0，则会执行分页查询；如果执行出错，则默认1和10。
     */
    public static Integer[] handlePage(int start, int length) {
        int page;
        if (start == 0 && length == 0) {
            return new Integer[]{};
        }
        try {
            page = start / length + 1;
        } catch (Exception e) {
            return new Integer[]{1, 10};
        }
        return new Integer[]{page, length};
    }


    /**
     * 下划线 转 驼峰
     */
    private static String upperCamel(String s) {
        return CaseFormat.UPPER_CAMEL.to(CaseFormat.LOWER_UNDERSCORE, s);
    }

    /**
     * 驼峰转下划线
     */
    private static String getTableName(Class<?> classy) {
        Table tableName = classy.getAnnotation(Table.class);
        return tableName.name();
    }

    public Pagination<?> page(Integer start, Integer length, Integer totalRows, List<?> resultList) {
        Pagination pager = new Pagination();
        pager.setNumPerPage(null != length ? length : 10);
        pager.setCurrentPage(null != start ? start : 1);
        pager.setTotalRows(null != totalRows ? totalRows : 0);
        pager.setTotalPages();
        pager.setResultList(resultList);
        pager.setLastIndex();
        pager.setStartIndex();
        return pager;
    }

    /**
     * 使用于公司前端框架的分页参数
     */
    public static class Pagination<T> {
        /**
         * 一页显示的记录数
         */
        private int numPerPage = 10;
        /**
         * 记录总数
         */
        private int totalRows;
        /**
         * 总页数
         */
        private int totalPages;
        /**
         * 当前页码
         */
        private int currentPage = 1;
        /**
         * 起始行数
         */
        private int startIndex;
        /**
         * 结束行数
         */
        private int lastIndex;
        /**
         * 结果集存放List
         */
        private List<T> resultList;

        private String orderBy;

        private String sort;

        /**
         * 计算总页数
         */
        void setTotalPages() {
            if (numPerPage == 0) {
                this.totalPages = 0;
            } else if (totalRows % numPerPage == 0) {
                this.totalPages = totalRows / numPerPage;
            } else {
                this.totalPages = (totalRows / numPerPage) + 1;
            }
        }

        /**
         * 计算结束时候的索引
         */
        void setLastIndex() {
            if (numPerPage == 0) {
                this.lastIndex = totalRows;
            } else if (totalRows < numPerPage) {
                this.lastIndex = totalRows;
            } else if ((totalRows % numPerPage == 0) || (totalRows % numPerPage != 0 && currentPage < totalPages)) {
                this.lastIndex = currentPage * numPerPage;
                //最后一页
            } else if (totalRows % numPerPage != 0 && currentPage == totalPages) {
                this.lastIndex = totalRows;
            }
        }

        public int getCurrentPage() {
            return currentPage;
        }

        public void setCurrentPage(int currentPage) {
            this.currentPage = currentPage == 0 ? 1 : currentPage;
        }

        public int getNumPerPage() {
            return numPerPage;
        }

        public void setNumPerPage(int numPerPage) {
            this.numPerPage = numPerPage;
        }

        public List<T> getResultList() {
            return resultList;
        }

        public void setResultList(List<T> resultList) {
            this.resultList = resultList;
        }

        public int getTotalPages() {
            return totalPages;
        }

        public int getTotalRows() {
            return totalRows;
        }

        public void setTotalRows(int totalRows) {
            this.totalRows = totalRows;
        }

        public int getStartIndex() {
            return startIndex;
        }

        void setStartIndex() {
            this.startIndex = (currentPage - 1) * numPerPage;
        }

        public int getLastIndex() {
            return lastIndex;
        }

        public String getOrderBy() {
            return orderBy;
        }

        public void setOrderBy(String orderBy) {
            this.orderBy = orderBy;
        }

        public String getSort() {
            return sort;
        }

        public void setSort(String sort) {
            this.sort = sort;
        }
    }

}
